/**
 * 数据库字段变更脚本
 * 为 player_deputy 表添加 pos 字段（上阵位置）
 */

const mysql = require('mysql2/promise');
// 使用与nestjs应用相同的数据库配置
const dbConfig = {
    host: process.env.DB_HOST || '43.143.253.188',
    port: parseInt(process.env.DB_PORT) || 3306,
    user: process.env.DB_USER || 'nextjs',
    password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5', // 注意：这里使用DB_PASS而不是DB_PASSWORD
    database: process.env.DB_NAME || 'nextjs',
    charset: 'utf8mb4'
};

class DatabaseMigration {
    constructor() {
        this.connection = null;
    }

    /**
     * 连接数据库
     */
    async connect() {
        try {
            console.log('🔌 正在连接数据库...');
            console.log(`   主机: ${dbConfig.host}`);
            console.log(`   端口: ${dbConfig.port}`);
            console.log(`   数据库: ${dbConfig.database}`);

            this.connection = await mysql.createConnection(dbConfig);

            console.log('✅ 数据库连接成功');
            return true;
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            return false;
        }
    }

    /**
     * 检查字段是否存在
     */
    async checkFieldExists(tableName, fieldName) {
        try {
            const [rows] = await this.connection.execute(
                `SELECT COLUMN_NAME 
                 FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_SCHEMA = ? 
                 AND TABLE_NAME = ? 
                 AND COLUMN_NAME = ?`,
                [dbConfig.database, tableName, fieldName]
            );

            return rows.length > 0;
        } catch (error) {
            console.error(`❌ 检查字段 ${fieldName} 是否存在时出错:`, error.message);
            return false;
        }
    }

    /**
     * 检查表是否存在
     */
    async checkTableExists(tableName) {
        try {
            const [rows] = await this.connection.execute(
                `SELECT TABLE_NAME 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = ? 
                 AND TABLE_NAME = ?`,
                [dbConfig.database, tableName]
            );

            return rows.length > 0;
        } catch (error) {
            console.error(`❌ 检查表 ${tableName} 是否存在时出错:`, error.message);
            return false;
        }
    }

    /**
     * 获取表结构信息
     */
    async getTableStructure(tableName) {
        try {
            const [rows] = await this.connection.execute(`DESCRIBE ${tableName}`);
            return rows;
        } catch (error) {
            console.error(`❌ 获取表 ${tableName} 结构时出错:`, error.message);
            return [];
        }
    }

    /**
     * 添加 pos 字段到 player_deputy 表
     */
    async addPosField() {
        try {
            console.log('\n📋 开始处理 player_deputy 表...');

            // 1. 检查表是否存在
            const tableExists = await this.checkTableExists('player_deputy');
            if (!tableExists) {
                console.log('❌ 表 player_deputy 不存在');
                return false;
            }
            console.log('✅ 表 player_deputy 存在');

            // 2. 检查 pos 字段是否已存在
            const fieldExists = await this.checkFieldExists('player_deputy', 'pos');
            if (fieldExists) {
                console.log('✅ 字段 pos 已存在，跳过添加');
                return true;
            }

            // 3. 显示当前表结构
            console.log('\n📊 当前表结构:');
            const structure = await this.getTableStructure('player_deputy');
            structure.forEach(field => {
                console.log(`   ${field.Field}: ${field.Type} ${field.Null === 'NO' ? 'NOT NULL' : 'NULL'} ${field.Default ? `DEFAULT ${field.Default}` : ''}`);
            });

            // 4. 添加 pos 字段
            console.log('\n🔧 正在添加 pos 字段...');
            await this.connection.execute(`
                ALTER TABLE player_deputy 
                ADD COLUMN pos INT DEFAULT 0 COMMENT '上阵位置 (0:未上阵, 1-3:上阵位置)' 
                AFTER is_deployed
            `);

            console.log('✅ pos 字段添加成功');

            // 5. 显示更新后的表结构
            console.log('\n📊 更新后的表结构:');
            const newStructure = await this.getTableStructure('player_deputy');
            newStructure.forEach(field => {
                console.log(`   ${field.Field}: ${field.Type} ${field.Null === 'NO' ? 'NOT NULL' : 'NULL'} ${field.Default ? `DEFAULT ${field.Default}` : ''}`);
            });

            return true;
        } catch (error) {
            console.error('❌ 添加 pos 字段失败:', error.message);
            return false;
        }
    }

    /**
     * 更新现有数据的 pos 字段
     */
    async updateExistingData() {
        try {
            console.log('\n🔄 正在更新现有数据...');

            // 查询当前数据
            const [rows] = await this.connection.execute(
                'SELECT id, deputy_id, player_id, is_deployed, pos FROM player_deputy LIMIT 10'
            );

            if (rows.length === 0) {
                console.log('ℹ️  没有现有数据需要更新');
                return true;
            }

            console.log(`📊 找到 ${rows.length} 条记录（显示前10条）:`);
            rows.forEach((row, index) => {
                console.log(`   ${index + 1}. ID: ${row.id}, Deputy: ${row.deputy_id}, Player: ${row.player_id}, Deployed: ${row.is_deployed}, Pos: ${row.pos}`);
            });

            // 根据 is_deployed 状态更新 pos 字段
            console.log('\n🔧 正在根据 is_deployed 状态更新 pos 字段...');

            // 将 is_deployed = true 的记录按 player_id 分组，并分配 pos 1, 2, 3
            const [deployedRows] = await this.connection.execute(
                'SELECT player_id, deputy_id FROM player_deputy WHERE is_deployed = true ORDER BY player_id, id'
            );

            if (deployedRows.length > 0) {
                const playerGroups = {};
                deployedRows.forEach(row => {
                    if (!playerGroups[row.player_id]) {
                        playerGroups[row.player_id] = [];
                    }
                    playerGroups[row.player_id].push(row.deputy_id);
                });

                // 为每个玩家的上阵副将分配位置
                for (const [playerId, deputyIds] of Object.entries(playerGroups)) {
                    for (let i = 0; i < Math.min(deputyIds.length, 3); i++) {
                        await this.connection.execute(
                            'UPDATE player_deputy SET pos = ? WHERE player_id = ? AND deputy_id = ?',
                            [i + 1, playerId, deputyIds[i]]
                        );
                    }
                }

                console.log(`✅ 已更新 ${deployedRows.length} 条上阵副将的位置信息`);
            }

            // 确保所有 is_deployed = false 的记录 pos = 0
            const [result] = await this.connection.execute(
                'UPDATE player_deputy SET pos = 0 WHERE is_deployed = false AND pos != 0'
            );

            if (result.affectedRows > 0) {
                console.log(`✅ 已将 ${result.affectedRows} 条下阵副将的 pos 设置为 0`);
            }

            return true;
        } catch (error) {
            console.error('❌ 更新现有数据失败:', error.message);
            return false;
        }
    }

    /**
     * 验证数据完整性
     */
    async validateData() {
        try {
            console.log('\n🔍 正在验证数据完整性...');

            // 检查是否有 is_deployed = true 但 pos = 0 的记录
            const [deployedWithoutPos] = await this.connection.execute(
                'SELECT COUNT(*) as count FROM player_deputy WHERE is_deployed = true AND pos = 0'
            );

            if (deployedWithoutPos[0].count > 0) {
                console.log(`⚠️  发现 ${deployedWithoutPos[0].count} 条上阵但位置为0的记录`);
            } else {
                console.log('✅ 所有上阵副将都有正确的位置');
            }

            // 检查是否有 is_deployed = false 但 pos > 0 的记录
            const [notDeployedWithPos] = await this.connection.execute(
                'SELECT COUNT(*) as count FROM player_deputy WHERE is_deployed = false AND pos > 0'
            );

            if (notDeployedWithPos[0].count > 0) {
                console.log(`⚠️  发现 ${notDeployedWithPos[0].count} 条下阵但位置大于0的记录`);
            } else {
                console.log('✅ 所有下阵副将的位置都为0');
            }

            // 检查每个玩家的上阵副将数量
            const [playerStats] = await this.connection.execute(`
                SELECT player_id, COUNT(*) as deployed_count 
                FROM player_deputy 
                WHERE is_deployed = true 
                GROUP BY player_id 
                HAVING deployed_count > 3
            `);

            if (playerStats.length > 0) {
                console.log(`⚠️  发现 ${playerStats.length} 个玩家的上阵副将超过3个:`);
                playerStats.forEach(stat => {
                    console.log(`   玩家 ${stat.player_id}: ${stat.deployed_count} 个上阵副将`);
                });
            } else {
                console.log('✅ 所有玩家的上阵副将数量都符合要求（≤3个）');
            }

            return true;
        } catch (error) {
            console.error('❌ 验证数据完整性失败:', error.message);
            return false;
        }
    }

    /**
     * 关闭数据库连接
     */
    async close() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    /**
     * 执行完整的迁移流程
     */
    async run() {
        console.log('🚀 开始数据库字段变更迁移...\n');

        try {
            // 1. 连接数据库
            const connected = await this.connect();
            if (!connected) {
                return false;
            }

            // 2. 添加 pos 字段
            const fieldAdded = await this.addPosField();
            if (!fieldAdded) {
                return false;
            }

            // 3. 更新现有数据
            const dataUpdated = await this.updateExistingData();
            if (!dataUpdated) {
                return false;
            }

            // 4. 验证数据完整性
            const validated = await this.validateData();
            if (!validated) {
                return false;
            }

            console.log('\n🎉 数据库字段变更迁移完成！');
            console.log('\n📋 迁移总结:');
            console.log('   ✅ 已为 player_deputy 表添加 pos 字段');
            console.log('   ✅ 已更新现有数据的位置信息');
            console.log('   ✅ 已验证数据完整性');
            console.log('\n💡 现在可以使用 2072 指令进行副将上阵/下阵/位置交换操作了！');

            return true;
        } catch (error) {
            console.error('❌ 迁移过程中发生错误:', error.message);
            return false;
        } finally {
            await this.close();
        }
    }
}

// 运行迁移
async function main() {
    const migration = new DatabaseMigration();
    const success = await migration.run();

    if (success) {
        console.log('\n✅ 迁移成功完成');
        process.exit(0);
    } else {
        console.log('\n❌ 迁移失败');
        process.exit(1);
    }
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch((error) => {
        console.error('💥 脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = DatabaseMigration;
